﻿using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using SlamCms.SharePoint.Core;
using System.Linq;
using Slam.SharePoint;
using System.Collections.Generic;
using SlamCms.Common;
using System.Web;
using SlamCms.SharePoint.SlamProfiles.ContentItem;

namespace SlamCms.SharePoint.UtilityPages
{
	public partial class VerifyContentItems : LayoutsPageBase
	{
		#region Constants

		private const string ContentItemSyncStatusTableName = "ContentItemSyncStatus";

		#endregion

		#region Variables

		private List<ContentItemSyncStatusFixedReport> _updatedItemsMismatchingListRelativeID;
		private bool _fix = false;

		#endregion

		#region Events

		protected override void OnInit(EventArgs e)
		{
            btnVerifyPrevious.Click += new EventHandler(btnVerifyPrevious_Click);
			btnVerify.Click += new EventHandler(btnVerify_Click);
			btnVerifyAndSync.Click += new EventHandler(btnVerifyAndSync_Click);
			base.OnInit(e);
		}

		protected void assignNewSlamIdButton_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
		{
			string editLinkUrl = (string)e.CommandArgument;
			string altEditLinkUrl = "";
			SPSecurity.RunWithElevatedPrivileges(delegate()
			{
				using (SPSite site = new SPSite(editLinkUrl))
				{
					using (SPWeb web = site.OpenWeb())
					{
						var query = HttpUtility.ParseQueryString(new Uri(editLinkUrl).Query);

						SPList list = web.GetList(editLinkUrl);
						if (list != null)
						{
							SPListItem listItem = list.GetItemById(int.Parse(query["ID"]));
							if (listItem != null)
							{
								string slamId = listItem["SLAM ID"].ToString();
								altEditLinkUrl = SlamCmsContext.Data.Database.ExecuteReader<string>(String.Format("select top 1 EditLink from ContentItemSyncStatus where ContentItemID = '{0}' and EditLink != '{1}'", slamId, editLinkUrl)).FirstOrDefault();
								
								web.AllowUnsafeUpdates = true;
								listItem["SLAM ID"] = null;
								listItem["SLAM ID"] = listItem.GetUniqueId(true, false);
								listItem.SystemUpdate(false);
								SlamCmsContext.Data.Database.ExecuteNonQuery(String.Format("update ContentItemSyncStatus set ContentItemID = '{1}' where ContentItemID = '{0}' and EditLink = '{2}'", slamId, listItem["SLAM ID"].ToString(), editLinkUrl));

								this._fix = false;
								Verify(true);
							}
						}
					}
				}

				if (!String.IsNullOrEmpty(altEditLinkUrl))
				{
					using (SPSite site = new SPSite(altEditLinkUrl))
					{
						using (SPWeb web = site.OpenWeb())
						{
							var query = HttpUtility.ParseQueryString(new Uri(altEditLinkUrl).Query);

							SPList list = web.GetList(editLinkUrl);
							if (list != null)
							{
								SPListItem listItem = list.GetItemById(int.Parse(query["ID"]));
								if (listItem != null)
								{
									web.AllowUnsafeUpdates = true;
									listItem.SlamUpdate(false, false, false);

									this._fix = false;
									Verify(true);
								}
							}
						}
					}
				}
			});
		}

		protected void fixMissingContentItemButton_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
		{
			string editLinkUrl = (string)e.CommandArgument;
			SPSecurity.RunWithElevatedPrivileges(delegate()
			{
				using (SPSite site = new SPSite(editLinkUrl))
				{
					using (SPWeb web = site.OpenWeb())
					{
						var query = HttpUtility.ParseQueryString(new Uri(editLinkUrl).Query);
						
						SPList list = web.GetList(editLinkUrl);
						if (list != null)
						{
							SPListItem listItem = list.GetItemById(int.Parse(query["ID"]));
							if (listItem != null)
							{
								web.AllowUnsafeUpdates = true;
								listItem.SlamUpdate(false, false, false);
								SlamCmsContext.Data.Database.ExecuteNonQuery(String.Format("update ContentItemSyncStatus set Missing = 0 where ContentItemID = '{0}'", listItem["SLAM ID"].ToString()));

								this._fix = false;
								Verify(true);
							}
						}
					}
				}
			});
		}

		protected void fixMismatchedStatusButton_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
		{
			string editLinkUrl = (string)e.CommandArgument;
			SPSecurity.RunWithElevatedPrivileges(delegate()
			{
				using (SPSite site = new SPSite(editLinkUrl))
				{
					using (SPWeb web = site.OpenWeb())
					{
						var query = HttpUtility.ParseQueryString(new Uri(editLinkUrl).Query);

						SPList list = web.GetList(editLinkUrl);
						if (list != null)
						{
							SPListItem listItem = list.GetItemById(int.Parse(query["ID"]));
							if (listItem != null)
							{
								web.AllowUnsafeUpdates = true;
								listItem.SlamUpdate(false, false, false);
								SlamCmsContext.Data.Database.ExecuteNonQuery(String.Format("update ContentItemSyncStatus set Status = SharePointStatus where ContentItemID = '{0}'", listItem["SLAM ID"].ToString()));

								this._fix = false;
								Verify(true);
							}
						}
					}
				}
			});
		}

		protected void fixMissingSharePointItemButton_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
		{
			string slamId = (string)e.CommandArgument;

			SlamCmsContext.Data.Database.ExecuteNonQuery(String.Format("update ContentItem set StatusID = 6 where PrimaryContentItemID = '{0}'", slamId.Replace("-p", "")));

			this._fix = false;
			Verify(true);
		}

		protected void fixMismatchListRelativeIdButton_Command(object sender, System.Web.UI.WebControls.CommandEventArgs e)
		{
			string editLinkUrl = (string)e.CommandArgument;
			SPSecurity.RunWithElevatedPrivileges(delegate()
			{
				using (SPSite site = new SPSite(editLinkUrl))
				{
					using (SPWeb web = site.OpenWeb())
					{
						var query = HttpUtility.ParseQueryString(new Uri(editLinkUrl).Query);

						SPList list = web.GetList(editLinkUrl);
						if (list != null)
						{
							SPListItem listItem = list.GetItemById(int.Parse(query["ID"]));
							if (listItem != null)
							{
								UpdateListRelativeID(listItem);
								SlamCmsContext.Data.Database.ExecuteNonQuery(String.Format("update ContentItemSyncStatus set ListRelativeIDMismatch = 0 where ContentItemID = '{0}'", listItem["SLAM ID"].ToString()));

								this._fix = false;
								Verify(true);
							}
						}
					}
				}
			});
		}

		protected void Page_Load(object sender, EventArgs e)
		{
		}

        void btnVerifyPrevious_Click(object sender, EventArgs e)
        {
            this._fix = false;
            Verify(true);
        }

		void btnVerify_Click(object sender, EventArgs e)
		{
			this._fix = false;
			Verify(false);
		}

		void btnVerifyAndSync_Click(object sender, EventArgs e)
		{
			this._fix = true;
			Verify(false);
		}


		#endregion

		#region Methods

		private void CleanGrids()
		{
			this.gridContentItemNotFound.DataSource = null;
			this.gridContentItemNotFound.DataBind();
			this.gridDuplicateSlamIds.DataSource = null;
			this.gridDuplicateSlamIds.DataBind();
			this.gridListID.DataSource = null;
			this.gridListID.DataBind();
			this.gridListRelativeID.DataSource = null;
			this.gridListRelativeID.DataBind();
			this.gridSharePointNotFound.DataSource = null;
			this.gridSharePointNotFound.DataBind();
			this.gridMismatchingStatuses.DataSource = null;
			this.gridMismatchingStatuses.DataBind();
		}

		private void InitControls()
		{
			CleanGrids();
			this.reports.Visible = true;

			if (_fix)
			{
				ltNotFoundContentItem.Text = "Updated ";
				ltListRelativeID.Text = "Updated ";
				ltSharePointNotFound.Text = "Updated ";
				_updatedItemsMismatchingListRelativeID = new List<ContentItemSyncStatusFixedReport>();
				gridListRelativeIDUpdated.Visible = true;
			}
			else
			{
				gridListRelativeID.Visible = true;
			}
		}

		/// <summary>
		/// Verify that content item and sharepoint items association is consitent
		/// </summary>
		private void Verify(bool showReportsOnly)
		{
			InitControls();
			VerifyContentItemSyncStatusTable();
            if (!showReportsOnly)
            {
                ClearTable();
                VerifySharePointContentItems();
            }
			BuildReports();
		
		}

		private void BuildReports()
		{
			BuildContentItemNotFoundReport();
			BuildListRelativeIDReport();
			BuldListIDReport();
			BuildSharePointNotFoundReport();
			BuildDuplicateSlamIdsReport();
			BuildMismatchingStatusesReport();
		}

		#region Reports

		private void BuildSharePointNotFoundReport()
		{
			string query = @"
			begin
				create table #SyncedContentItemIDs
				(
				ContentItemID varchar(25) primary key
				)
				insert into #SyncedContentItemIDs
				select distinct replace(ss.ContentItemID, '-p', '') ContentItemID
				from ContentItemSyncStatus ss
 
				  select ci.ContentItemID, ci.ReferenceTitle Title, st.Name Status, st.Name SharePointStatus, ('" + Request.Url.GetLeftPart(UriPartial.Authority) + @"' + sc.ServerRelativeUrl + s.Url + l.EditFormUrl + '?ID=' + CAST(ci.ListRelativeID as varchar)) as EditLink  
											 from ContentItem ci
											 join SLAM.List l on l.ListID = ci.ListID
                                             join Status st on st.StatusID = ci.StatusID 
											 join Slam.Site s on l.SiteID = s.SiteID
											 join SLAM.SiteCollection sc on s.SiteCollectionID = sc.SiteCollectionID
											 where sc.SiteCollectionID = '" + Site.ID.ToString() + @"' and ci.StatusID != 6 and NOT EXISTS(select * from #SyncedContentItemIDs ss where ci.PrimaryContentItemID = ss.ContentItemID)
				drop table #SyncedContentItemIDs
			end";
			
			var result = SlamCmsContext.Data.Database.ExecuteReader<ContentItemSyncStatusReport>(query);
			if (result.Count() > 0)
			{
				this.gridSharePointNotFound.DataSource = result;
				this.gridSharePointNotFound.DataBind();
			}
		}

		private void BuldListIDReport()
		{
			string query = @"select ContentItemID,Title,Status,SharePointStatus,EditLink from ContentItemSyncStatus
							 where ListIDMismatch = 1 and Status != 'Retired' and Status != ''";

			var result = SlamCmsContext.Data.Database.ExecuteReader<ContentItemSyncStatusReport>(query);
			if (result.Count() > 0)
			{
				this.gridListID.DataSource = result;
				this.gridListID.DataBind();
			}
		}

		private void BuildListRelativeIDReport()
		{
			if (_fix)
			{
				if (_updatedItemsMismatchingListRelativeID.Count > 0)
				{
					this.gridListRelativeIDUpdated.DataSource = _updatedItemsMismatchingListRelativeID;
					this.gridListRelativeIDUpdated.DataBind();
				}
			}
			else
			{
				string query = @"select ContentItemID,Title,Status,SharePointStatus,EditLink
								from ContentItemSyncStatus
								where ListRelativeIDMismatch = 1 and Status != 'Retired' and Status != '' and ContentItemID not in
								(
								select	ContentItemID
								from	ContentItemSyncStatus
								group by ContentItemID
								having COUNT(*) > 1)";

				var result = SlamCmsContext.Data.Database.ExecuteReader<ContentItemSyncStatusReport>(query);

				if (result.Count() > 0)
				{
					this.gridListRelativeID.DataSource = result;
					this.gridListRelativeID.DataBind();
				}
			}
		}

		private void BuildContentItemNotFoundReport()
		{
			string query = @"select ContentItemID,Title,Status,SharePointStatus,EditLink from ContentItemSyncStatus
							 where Missing = 1 and (Title is not null or Title != '') and Status != 'Retired' and Status != ''";

			var result = SlamCmsContext.Data.Database.ExecuteReader<ContentItemSyncStatusReport>(query);
			if (result.Count() > 0)
			{
				this.gridContentItemNotFound.DataSource = result;
				this.gridContentItemNotFound.DataBind();
			}
		}

		private void BuildDuplicateSlamIdsReport()
		{
			string query = @"select * from ContentItemSyncStatus ciss,
							(
							select	ContentItemID
							from	ContentItemSyncStatus
							group by ContentItemID
							having COUNT(*) > 1) a
							where	ciss.ContentItemID = a.ContentItemID
							order by ciss.ContentItemID";

			var result = SlamCmsContext.Data.Database.ExecuteReader<ContentItemSyncStatusReport>(query);
			if (result.Count() > 0)
			{
				this.gridDuplicateSlamIds.DataSource = result;
				this.gridDuplicateSlamIds.DataBind();
			}
		}

		private void BuildMismatchingStatusesReport()
		{
			string query = @"select ContentItemID,Title,Status,SharePointStatus,EditLink from ContentItemSyncStatus
							 where Status != SharePointStatus";

			var result = SlamCmsContext.Data.Database.ExecuteReader<ContentItemSyncStatusReport>(query);
			if (result.Count() > 0)
			{
				this.gridMismatchingStatuses.DataSource = result;
				this.gridMismatchingStatuses.DataBind();
			}
		}

		#endregion

		/// <summary>
		/// Check that sharepoint items have their respective content item on the database and that the data is consistent with the sharepoint item.
		/// </summary>
		private void VerifySharePointContentItems()
		{
			foreach (SPWeb site in SPContext.Current.Site.AllWebs)
			{
				foreach (SPList list in site.Lists)
				{
					if (list.Fields.ContainsFieldWithStaticName("SLAM_x0020_ID") && list.EnableVersioning && list.EnableModeration && list.ItemCount > 0)
					{
						var itemsSlamId = list.Items.Cast<SPListItem>().Where(i => !Convert.ToString(i["SLAM ID"]).IsNullOrEmpty()).Select(i => Convert.ToString(i["SLAM ID"]).Replace("-p", "")).ToArray();
						if(list.BaseTemplate == SPListTemplateType.DocumentLibrary || list.BaseTemplate == SPListTemplateType.PictureLibrary)
							itemsSlamId = itemsSlamId.Concat(list.Folders.Cast<SPListItem>().Where(i => !Convert.ToString(i["SLAM ID"]).IsNullOrEmpty()).Select(i => Convert.ToString(i["SLAM ID"]).Replace("-p", "")).ToArray()).ToArray();

						var contentItems = SlamCmsContext.Data.CreateQuery().FilterContentItemId(itemsSlamId).FilterStatus(SlamCms.Data.FilterStatus.PreviewMode).Cache(SlamCms.Data.QueryCacheBehavior.NoCache).Get().ToList();
						contentItems = contentItems.Concat(SlamCmsContext.Data.CreateQuery().FilterContentItemId(itemsSlamId).FilterStatus(SlamCms.Data.FilterStatus.LiveOrRetired).Cache(SlamCms.Data.QueryCacheBehavior.NoCache).Get().ToList()).ToList();

						foreach (SPListItem item in list.Items)
						{
							if (!Convert.ToString(item["SLAM ID"]).IsNullOrEmpty())
							{
								var contentItem = contentItems.Where(o => o.ContentItemId.Equals(Convert.ToString(item["SLAM ID"]))).FirstOrDefault();
								ContentItemSyncStatus ciSyncStatus;

								if (contentItem == null)
								{
									ciSyncStatus = new ContentItemSyncStatus(true, false, false);

									if (_fix && item.ModerationInformation.Status != SPModerationStatusType.Denied)
									{
										item.SlamUpdate(false, false, false);
									}
								}
								else
								{
									if (!contentItem.ListId.Equals(list.ID))
									{
										ciSyncStatus = new ContentItemSyncStatus(false, true, false);
									}
									else
									{
										if (!contentItem.ListRelativeId.Equals(item.ID))
										{
											ciSyncStatus = new ContentItemSyncStatus(false, false, true);
											if (_fix)
											{
												ContentItemSyncStatusFixedReport fixedItem = new ContentItemSyncStatusFixedReport();
												fixedItem.ContentItemID = Convert.ToString(item["SLAM ID"]);
												fixedItem.Title = item.Title;
												fixedItem.EditLink = site.Url.TrimEnd('/') + "/" + list.Forms[PAGETYPE.PAGE_EDITFORM].Url + "?ID=" + contentItem.ListRelativeId.ToString();
												fixedItem.NewEditLink = site.Url.TrimEnd('/') + "/" + list.Forms[PAGETYPE.PAGE_EDITFORM].Url + "?ID=" + item.ID.ToString();
												this._updatedItemsMismatchingListRelativeID.Add(fixedItem);
												UpdateListRelativeID(item);
											}
										}
										else
										{
											ciSyncStatus = new ContentItemSyncStatus(false, false, false);
										}
									}
								}

								if (item.Fields.ContainsFieldWithStaticName("Publishing_x0020_Status"))
									ciSyncStatus.SharePointStatus = item.GetPublishingStatus();

								if (contentItem != null)
									ciSyncStatus.Status = contentItem.Status.Name;
								else
									ciSyncStatus.Status = ciSyncStatus.SharePointStatus;

								ciSyncStatus.ContentItemID = item["SLAM ID"].ToString();
								ciSyncStatus.Title = item.Title;
								ciSyncStatus.EditLink = site.Url.TrimEnd('/') + "/" + list.Forms[PAGETYPE.PAGE_EDITFORM].Url + "?ID=" + item.ID.ToString();

								ContentItemSyncStatusTableInsert(ciSyncStatus);
							}
						}

						if (list.BaseTemplate == SPListTemplateType.DocumentLibrary || list.BaseTemplate == SPListTemplateType.PictureLibrary)
						{
							foreach (SPListItem item in list.Folders)
							{
								if (!Convert.ToString(item["SLAM ID"]).IsNullOrEmpty())
								{
									var contentItem = contentItems.Where(o => o.ContentItemId.Equals(Convert.ToString(item["SLAM ID"]))).FirstOrDefault();
									ContentItemSyncStatus ciSyncStatus;

									if (contentItem == null)
									{
										ciSyncStatus = new ContentItemSyncStatus(true, false, false);
										if (_fix && item.ModerationInformation.Status != SPModerationStatusType.Denied)
										{
											item.SlamUpdate(false, false, false);
										}
									}
									else
									{
										if (!contentItem.ListId.Equals(list.ID))
										{
											ciSyncStatus = new ContentItemSyncStatus(false, true, false);
										}
										else
										{
											if (!contentItem.ListRelativeId.Equals(item.ID))
											{
												ciSyncStatus = new ContentItemSyncStatus(false, false, true);
												if (_fix)
												{
													ContentItemSyncStatusFixedReport fixedItem = new ContentItemSyncStatusFixedReport();
													fixedItem.ContentItemID = Convert.ToString(item["SLAM ID"]);
													fixedItem.Title = item.Title;
													fixedItem.EditLink = site.Url.TrimEnd('/') + "/" + list.Forms[PAGETYPE.PAGE_EDITFORM].Url + "?ID=" + contentItem.ListRelativeId.ToString();
													fixedItem.NewEditLink = site.Url.TrimEnd('/') + "/" + list.Forms[PAGETYPE.PAGE_EDITFORM].Url + "?ID=" + item.ID.ToString();
													this._updatedItemsMismatchingListRelativeID.Add(fixedItem);
													UpdateListRelativeID(item);
												}
											}
											else
											{
												ciSyncStatus = new ContentItemSyncStatus(false, false, false);
											}
										}
									}

									if (item.Fields.ContainsFieldWithStaticName("Publishing_x0020_Status"))
										ciSyncStatus.SharePointStatus = item.GetPublishingStatus();

									if (contentItem != null)
										ciSyncStatus.Status = contentItem.Status.Name;
									else 
										ciSyncStatus.Status = ciSyncStatus.SharePointStatus;

									ciSyncStatus.ContentItemID = item["SLAM ID"].ToString();
									ciSyncStatus.Title = item.Title;
									ciSyncStatus.EditLink = site.Url.TrimEnd('/') + "/" + list.Forms[PAGETYPE.PAGE_EDITFORM].Url + "?ID=" + item.ID.ToString();

									ContentItemSyncStatusTableInsert(ciSyncStatus);
								}
							}
						}
					}
				}
				site.Dispose();
			}

			if (_fix)
				UpdateSharePointNotFoundItems();

		}

		#region Database Methods

		private void UpdateListRelativeID(SPListItem item)
		{
			string updateScript = String.Format(@"UPDATE [dbo].[ContentItem]
												  SET ListRelativeID = {0}
												   WHERE PrimaryContentItemID = '{1}'", item.ID, item["SLAM ID"].ToString().Replace("-p", ""));

			SlamCmsContext.Data.Database.ExecuteNonQuery(updateScript);
		}

		private void UpdateSharePointNotFoundItems()
		{
			string updateScript = @"UPDATE [dbo].[ContentItem] 
									SET StatusID = 6
									WHERE NOT EXISTS(select * from ContentItemSyncStatus ss where [dbo].[ContentItem].PrimaryContentItemID = replace(ss.ContentItemID, '-p', ''))";

			SlamCmsContext.Data.Database.ExecuteNonQuery(updateScript);
		}


		private void VerifyContentItemSyncStatusTable()
		{
			string existsScript = String.Format(@"IF (not exists (select 1 from information_schema.tables where table_name = '{0}')) 
									BEGIN
										select CAST(0 as bit)
									END
									ELSE
										BEGIN
											select CAST(1 as bit)
										END", ContentItemSyncStatusTableName);

			var result = SlamCmsContext.Data.Database.ExecuteScalar<bool>(existsScript);

			if (!result.Value)
			{
				string createScript = String.Format(@"CREATE TABLE [dbo].[{0}](
										[ContentItemID] [varchar](25) NOT NULL,
										[Title] [varchar](255) NOT NULL,
                                        [Status] [varchar](255) NOT NULL,
										[SharePointStatus] [varchar](255) NOT NULL,
										[EditLink] [varchar](2000) NOT NULL,
										[Missing] [bit] NOT NULL,
										[ListIDMismatch] [bit] NOT NULL,
										[ListRelativeIDMismatch] [bit] NOT NULL
										)", ContentItemSyncStatusTableName);

				SlamCmsContext.Data.Database.ExecuteNonQuery(createScript);
			}
		}

		private void ClearTable()
		{
			string clearScript = String.Format(@"DELETE FROM [dbo].[{0}]", ContentItemSyncStatusTableName);
			SlamCmsContext.Data.Database.ExecuteNonQuery(clearScript);
		}

		private void ContentItemSyncStatusTableInsert(ContentItemSyncStatus item)
		{
			string insertScript = String.Format(@"INSERT INTO [dbo].[ContentItemSyncStatus]
									([ContentItemID],[Title],[Status],[SharePointStatus],[EditLink],[Missing],[ListIDMismatch],[ListRelativeIDMismatch])
									VALUES('{0}','{1}','{2}','{3}','{4}',{5},{6},{7})", item.ContentItemID, item.Title.Replace("'", "''"), item.Status, item.SharePointStatus, item.EditLink, Convert.ToInt32(item.Missing), Convert.ToInt32(item.ListIDMismatch), Convert.ToInt32(item.ListRelativeIDMismatch));

			SlamCmsContext.Data.Database.ExecuteNonQuery(insertScript);
		}

		#endregion
		#endregion
	}

	public class ContentItemSyncStatus
	{
		public string ContentItemID { get; set; }
		public string Title { get; set; }
        public string Status { get; set; }
		public string SharePointStatus { get; set; }
		public string EditLink { get; set; }
		public bool Missing { get; set; }
		public bool ListIDMismatch { get; set; }
		public bool ListRelativeIDMismatch { get; set; }

		public ContentItemSyncStatus()
		{
		  
		}

		public ContentItemSyncStatus(bool Missing, bool ListIDMismatch, bool ListRelativeIDMismatch)
		{
			this.Missing = Missing;
			this.ListRelativeIDMismatch = ListRelativeIDMismatch;
			this.ListIDMismatch = ListIDMismatch;
		}
	}

	public class ContentItemSyncStatusReport
	{
		public string ContentItemID { get; set; }
		public string Title { get; set; }
        public string Status { get; set; }
		public string SharePointStatus { get; set; }
		public string EditLink { get; set; }
	}

	public class ContentItemSyncStatusFixedReport : ContentItemSyncStatusReport
	{
		public string NewEditLink { get; set; }
	}
}
